package com.commons.dbutils.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;

import javax.sql.DataSource;

import org.apache.commons.dbutils.AbstractQueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.log4j.Logger;

/**
 * 借鉴自dbutils工具类
 * @author wangjianshen
 *
 */
public class SqlRunner extends AbstractQueryRunner implements Runner{
	private static final Logger logger = Logger.getLogger(SqlRunner.class.getName());

	//设置查询超时时间
	public int readTimeOut=2;
	//设置insert和update的时间
	private int insertOrUpdateTimeOut=5;
	
	//设置批量处理的时间
	private int batchTimeOut=60;
	
	/**
	 * 默认构造函数
	 */
	public SqlRunner() {
		super();
	}
	/**
	 * 带有查询时间限制的构造函数
	 */
	public SqlRunner(int readTimeOut,int insertOrUpdateTimeOut,int batchTimeOut) {
		super();
		this.setTimeOut(readTimeOut, insertOrUpdateTimeOut, batchTimeOut);
	}
	/**
	 * orderDrivers解决方案，true不去尝试，false去尝试
	 * @param pmdKnownBroken
	 */
	public SqlRunner(boolean pmdKnownBroken,int readTimeOut,int insertOrUpdateTimeOut,int batchTimeOut) {
		super(pmdKnownBroken);
		this.setTimeOut(readTimeOut, insertOrUpdateTimeOut, batchTimeOut);
	}
	/**
	 * 配置数据源构造函数
	 * @param ds
	 */
	public SqlRunner(DataSource ds,int readTimeOut,int insertOrUpdateTimeOut,int batchTimeOut) {
		super(ds);
		this.setTimeOut(readTimeOut, insertOrUpdateTimeOut, batchTimeOut);
	}
	/**
	 * 配置数据源构造函数,orderDrivers解决方案，true不去尝试，false去尝试
	 * @param ds
	 * @param pmdKnownBroken
	 */
	public SqlRunner(DataSource ds, boolean pmdKnownBroken,int readTimeOut,int insertOrUpdateTimeOut,int batchTimeOut) {
		super(ds, pmdKnownBroken);
		this.setTimeOut(readTimeOut, insertOrUpdateTimeOut, batchTimeOut);
	}
	
	/**
	 * 设置超时时间
	 * @param readTimeOut
	 * @param insertOrUpdateTimeOut
	 * @param batchTimeOut
	 */
	private void setTimeOut(int readTimeOut,int insertOrUpdateTimeOut,int batchTimeOut){
		if(readTimeOut>0){
			this.readTimeOut = readTimeOut;
		}
		if(insertOrUpdateTimeOut>0){
			this.readTimeOut = insertOrUpdateTimeOut;
		}
		if(batchTimeOut>0){
			this.readTimeOut = batchTimeOut;
		}
	}
	/**
	 * 批量处理通过Connection
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int[] batch(Connection conn, String sql, Object[][] params)
			throws SQLException {
		return this.batch(conn, false, sql, params);
	}
	/**
	 * 批量处理前提已经配置了数据源
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int[] batch(String sql, Object[][] params) throws SQLException {
		Connection conn = this.prepareConnection();
		return this.batch(conn, true, sql, params);
	}
	
	/**
	 * 批量处理
	 * @param conn
	 * @param closeConn 是否关闭Connection
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private int[] batch(Connection conn, boolean closeConn, String sql,
			Object[][] params) throws SQLException {
		if (conn == null) {
			throw new SQLException("Null connection");
		}

		if (sql == null) {
			if (closeConn) {
				close(conn);
			}
			throw new SQLException("Null SQL statement");
		}

		if (params == null) {
			if (closeConn) {
				close(conn);
			}
			throw new SQLException(
					"Null parameters. If parameters aren't need, pass an empty array.");
		}

		PreparedStatement stmt = null;
		int[] rows = null;
		long startTime =0,endTime =0;
		try {
			stmt = this.prepareStatement(conn, sql);
			stmt.setQueryTimeout(batchTimeOut);
			for (int i = 0; i < params.length; i++) {
				this.fillStatement(stmt, params[i]);
				stmt.addBatch();
			}
			startTime = System.currentTimeMillis();
			rows = stmt.executeBatch();
			endTime = System.currentTimeMillis();
		} catch (SQLException e) {
			this.rethrow(e, sql, (Object[]) params);
		} finally {
			close(stmt);
			if (closeConn) {
				close(conn);
			}
		}
		this.showDebugLog(sql,(endTime-startTime), params);
		return rows;
	}
	
	 /**
     * Execute an SQL SELECT query with a single replacement parameter. The
     * caller is responsible for closing the connection.
     * @param <T> The type of object that the handler returns
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param param The replacement parameter.
     * @param rsh The handler that converts the results into an object.
     * @return The object returned by the handler.
     * @throws SQLException if a database access error occurs
     * @deprecated Use {@link #query(Connection, String, ResultSetHandler, Object...)}
     */
    @Deprecated
    public <T> T query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
        return this.query(conn, false, sql, rsh, new Object[]{param});
    }

    /**
     * Execute an SQL SELECT query with replacement parameters.  The
     * caller is responsible for closing the connection.
     * @param <T> The type of object that the handler returns
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param params The replacement parameters.
     * @param rsh The handler that converts the results into an object.
     * @return The object returned by the handler.
     * @throws SQLException if a database access error occurs
     * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object...)} instead
     */
    @Deprecated
    public <T> T query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
                return this.query(conn, false, sql, rsh, params);
    }

    /**
     * Execute an SQL SELECT query with replacement parameters.  The
     * caller is responsible for closing the connection.
     * @param <T> The type of object that the handler returns
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param rsh The handler that converts the results into an object.
     * @param params The replacement parameters.
     * @return The object returned by the handler.
     * @throws SQLException if a database access error occurs
     */
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return this.query(conn, false, sql, rsh, params);
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters.  The
     * caller is responsible for closing the connection.
     * @param <T> The type of object that the handler returns
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param rsh The handler that converts the results into an object.
     * @return The object returned by the handler.
     * @throws SQLException if a database access error occurs
     */
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
        return this.query(conn, false, sql, rsh, (Object[]) null);
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @param rsh The handler used to create the result object from
     * the <code>ResultSet</code>.
     *
     * @return An object generated by the handler.
     * @throws SQLException if a database access error occurs
     * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
     */
    @Deprecated
    public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.query(conn, true, sql, rsh, new Object[]{param});
    }

    /**
     * Executes the given SELECT SQL query and returns a result object.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param params Initialize the PreparedStatement's IN parameters with
     * this array.
     *
     * @param rsh The handler used to create the result object from
     * the <code>ResultSet</code>.
     *
     * @return An object generated by the handler.
     * @throws SQLException if a database access error occurs
     * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
     */
    @Deprecated
    public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
        Connection conn = this.prepareConnection();

        return query(conn, true, sql, rsh, params);
    }

    /**
     * Executes the given SELECT SQL query and returns a result object.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from
     * the <code>ResultSet</code>.
     * @param params Initialize the PreparedStatement's IN parameters with
     * this array.
     * @return An object generated by the handler.
     * @throws SQLException if a database access error occurs
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.query(conn, true, sql, rsh, params);
    }

    /**
     * Executes the given SELECT SQL without any replacement parameters.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from
     * the <code>ResultSet</code>.
     *
     * @return An object generated by the handler.
     * @throws SQLException if a database access error occurs
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.query(conn, true, sql, rsh, (Object[]) null);
    }

    /**
     * Calls query after checking the parameters to ensure nothing is null.
     * @param conn The connection to use for the query call.
     * @param closeConn True if the connection should be closed, false otherwise.
     * @param sql The SQL statement to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values.
     * @return The results of the query.
     * @throws SQLException If there are database or parameter errors.
     */
    private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
            throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (rsh == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null ResultSetHandler");
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;
        T result = null;
        long startTime =0,endTime =0;
        try {
            stmt = this.prepareStatement(conn, sql);
            stmt.setQueryTimeout(readTimeOut);
            this.fillStatement(stmt, params);
            startTime=System.currentTimeMillis();
            rs = this.wrap(stmt.executeQuery());
            endTime = System.currentTimeMillis();
            result = rsh.handle(rs);

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            try {
                close(rs);
            } finally {
                close(stmt);
                if (closeConn) {
                    close(conn);
                }
            }
        }
        this.showDebugLog(sql,(endTime-startTime), params);
        return result;
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
     * parameters.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    public int update(Connection conn, String sql) throws SQLException {
        return this.update(conn, false, sql, (Object[]) null);
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
     * parameter.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    public int update(Connection conn, String sql, Object param) throws SQLException {
        return this.update(conn, false, sql, new Object[]{param});
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param params The query replacement parameters.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    public int update(Connection conn, String sql, Object... params) throws SQLException {
        return update(conn, false, sql, params);
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without
     * any replacement parameters. The <code>Connection</code> is retrieved
     * from the <code>DataSource</code> set in the constructor.  This
     * <code>Connection</code> must be in auto-commit mode or the update will
     * not be saved.
     *
     * @param sql The SQL statement to execute.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public int update(String sql) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, (Object[]) null);
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement with
     * a single replacement parameter.  The <code>Connection</code> is
     * retrieved from the <code>DataSource</code> set in the constructor.
     * This <code>Connection</code> must be in auto-commit mode or the
     * update will not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public int update(String sql, Object param) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, new Object[]{param});
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The
     * <code>Connection</code> is retrieved from the <code>DataSource</code>
     * set in the constructor.  This <code>Connection</code> must be in
     * auto-commit mode or the update will not be saved.
     *
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?')
     * parameters.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public int update(String sql, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, params);
    }

    /**
     * Calls update after checking the parameters to ensure nothing is null.
     * @param conn The connection to use for the update call.
     * @param closeConn True if the connection should be closed, false otherwise.
     * @param sql The SQL statement to execute.
     * @param params An array of update replacement parameters.  Each row in
     * this array is one set of update replacement values.
     * @return The number of rows updated.
     * @throws SQLException If there are database or parameter errors.
     */
    private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        PreparedStatement stmt = null;
        int rows = 0;	
        long startTime =0,endTime =0;
       
        try {
            stmt = this.prepareStatement(conn, sql);
            stmt.setQueryTimeout(insertOrUpdateTimeOut);
            this.fillStatement(stmt, params);
            startTime = System.currentTimeMillis();
            rows = stmt.executeUpdate();
            endTime =System.currentTimeMillis();
        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }
        this.showDebugLog(sql,(endTime-startTime), params);
        return rows;
    }
    
    
	private void showDebugLog(String sql,long useTime,Object... params){
    	if(logger.isDebugEnabled()){
    		
    		logger.debug(new StringBuffer(this.getClass().getName()).append(" Thread.").append(Thread.currentThread().getId()).append(" execute sql:").append(sql).append(".use time ").append(useTime).append("ms.").toString());
    		logger.debug(new StringBuffer(this.getClass().getName()).append(" Thread.").append(Thread.currentThread().getId()).append(" Parameters:").append(Arrays.deepToString(params)).toString());
    	}
    }
	
}
